package com.api.shopapi.service.impl;


import com.api.shopapi.entity.*;
import com.api.shopapi.entity.dto.ElderlyDTO;
import com.api.shopapi.entity.dto.RechargeDTO;
import com.api.shopapi.mapper.*;
import com.api.shopapi.service.ImportExcelService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service("ImportExcelServiceImplOne")
public class ImportExcelServiceImpl implements ImportExcelService {

    @Autowired
    private ZoneMapper zoneMapper;

    @Autowired
    private ElderlyMapper elderlyMapper;

    @Autowired
    private StaffMapper staffMapper;

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private AreaMapper areaMapper;

    @Autowired
    private RoleMapper roleMapper;

    @Autowired
    private RechargeMapper rechargeMapper;

    @Autowired
    private GovernmentMapper governmentMapper;



    @Autowired
    private RechargeGroupMapper rechargeGroupMapper;

    @Autowired
    private ElderlyClassMapper elderlyClassMapper;


    private final static char[] VERIFY_CODE = { '1', '0', 'X', '9', '8', '7',
            '6', '5', '4', '3', '2' }; // 18位身份证中最后一位校验码

    private final static int[] VERIFY_CODE_WEIGHT = { 7, 9, 10, 5, 8, 4, 2, 1,
            6, 3, 7, 9, 10, 5, 8, 4, 2 };// 18位身份证中，各个数字的生成校验码时的权值



    @Override
    public MyResult batchImport(String fileName, MultipartFile file, String staffCode, InputStream is) throws Exception {

//        List<Elderly> elderlyList = new ArrayList<>();
//        List<User> userList = new ArrayList<>();
//        Staff staff = staffMapper.getByStaffCode(staffCode);
//        List<Zone> zoneList = zoneMapper.getListByGovernmentCode(staff.getGovernmentCode());
//        List<ElderlyClass> elderlyClassList = elderlyClassMapper.getList(staff.getGovernmentCode());
//        System.out.println(elderlyClassList.toString());
//        String roleCode = staff.getRoleCode();
//        if (roleCode == null) {
//            return MyResult.Failed("该角色不存在");
//        }
//        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
//            throw new Exception("上传文件格式不正确");
//        }
//        boolean isExcel2003 = true;
//        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
//            isExcel2003 = false;
//        }
//
//
////        InputStream is = file.getInputStream();
//        Workbook wb = null;
//        if (isExcel2003) {
//            wb = new HSSFWorkbook(is);
//        } else {
//            wb = new XSSFWorkbook(is);
//        }
//
//        Sheet sheet = wb.getSheetAt(0);
//        if (sheet == null) {
//            return MyResult.Failed("文件不存在！");
//        }
//
//
//        Elderly elderly;
////        User user = null;
//
//        HashMap<String,String> phoneHashMap =new HashMap<>();
//        HashMap<String,String> idCardHashMap =new HashMap<>();
//
//        String repetitionPhone ="";
//        String repetitionIdCard="";
//        String errorName="";
//        String errorPhone="";
//        String errorIdCard="";
//        String errorHomeAddress="";
//        String errorElderlyClass="";
//        String errorZone="";
//
//        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
//            if(r==1){
//                continue;
//            }
//            Row row = sheet.getRow(r); //通过sheet表单对象 得到 行对象
//
//            if (row == null) {
//                continue;
//            }
//
//            for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
//                if (row.getCell(cellIndex) == null) {
//                    continue;
//                }
//            }
//
//
//            elderly = new Elderly();
//
//            boolean isError=false;
//
//            if(row.getCell(0)==null){
//                continue;
//            }
//
//            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
//            String elderlyName = row.getCell(0).getStringCellValue().replace(" ", "");//得到每一行第一个单元格的值
//            if (elderlyName == null || elderlyName.isEmpty()) {
//                isError=true;
//                errorName=errorName.length()>0?errorName+"、"+(r + 1):(r + 1)+"";
//            }
//
//            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); //第二格
//            String phone = row.getCell(1).getStringCellValue().replace(" ", "");
//            if (phone.trim().length() != 11) {
//                isError=true;
//                errorPhone=errorPhone.length()>0?errorPhone+"、"+(r + 1):(r + 1)+"";
//            }
//
//            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
//            String idCard = row.getCell(2).getStringCellValue().replace(" ", "");  //第三格
//            if (!check(idCard)) {
//                isError=true;
//                errorIdCard=errorIdCard.length()>0?errorIdCard+"、"+(r + 1):(r + 1)+"";
//            }
//
//
//            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); //第四格
//            String homeAddress = row.getCell(3).getStringCellValue();
////            if (homeAddress == null || homeAddress.isEmpty()) {
////                isError=true;
////                errorHomeAddress=errorHomeAddress.length()>0?errorHomeAddress+"、"+(r + 1):(r + 1)+"";
////            }
//
//
//            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
//            String elderlyClass = row.getCell(4).getStringCellValue().replace(" ", "");  //第三格
//            int elderlyClassValue=-1;
//            for(ElderlyClass elderlyClass1:elderlyClassList){
//                if(elderlyClass1.getName().equals(elderlyClass)){
//                    elderlyClassValue=elderlyClass1.getValue();
//                }
//            }
//            if(elderlyClassValue==-1){
//                errorElderlyClass=errorElderlyClass.length()>0?errorElderlyClass+"、"+(r + 1):(r + 1)+"";
//                isError=true;
//            }
//
//            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
//            String zone = row.getCell(5).getStringCellValue().replace(" ", "");  //第三格
//            String zoneCode="";
//            String zoneNameLast="";
//            String zoneCodePath ="";
//            String zoneName ="";
//            for(Zone zone1:zoneList){
//                if(zone1.getName().equals(zone)){
//                    zoneCode=zone1.getZoneCode();
//                    zoneNameLast=zone1.getName();
//                }
//            }
//            if(zoneCode.equals("")){
//                errorZone=errorZone.length()>0?errorZone+"、"+(r + 1):(r + 1)+"";
//                isError=true;
//            }else {
//                zoneCodePath = getZonePathAndName(zoneList,zoneCode,1,zoneCode);
//                zoneName = getZonePathAndName(zoneList,zoneCode,2,zoneNameLast);
//            }
//
//
////            if(elderlyClass.equals("是")){
////
////            }
////            if (!check(idCard)) {
////                isError=true;
////                errorIdCard=errorIdCard.length()>0?errorIdCard+"、"+(r + 1):(r + 1)+"";
////            }
//
//
//
//            if(!phoneHashMap.containsKey(phone)){
//                phoneHashMap.put(phone,phone);
//            }else {
//                isError=true;
//                repetitionPhone=repetitionPhone.equals("")?phone:repetitionPhone+"、"+phone;
//            }
//
//
//            if(!idCardHashMap.containsKey(idCard)){
//                idCardHashMap.put(idCard,idCard);
//            }else {
//                isError=true;
//                repetitionIdCard=repetitionIdCard.equals("")?idCard:repetitionIdCard+"、"+idCard;
//            }
//
//
//            if(isError){
//                continue;
//            }
//
//
//            String userCode = UUID.randomUUID().toString();
//            String idNumber=idCard.length()==15?contertToNewCardNumber(idCard):idCard;
//            String genderNumber = idNumber.substring(16, 17);
//            int gender=Integer.parseInt(genderNumber)%2;
//            int age = judgeAge(idNumber);
//            String elderlyCode = UUID.randomUUID().toString();
//
//            elderly.setElderlyCode(elderlyCode);
//            elderly.setIdCard(idCard);
//            elderly.setUserCode(userCode);
//            elderly.setBalance(0);
//            elderly.setGender(gender);
//            elderly.setBirthday(getBirthday(idNumber));
//            elderly.setAge(age);
//            elderly.setPhone(phone);
//            elderly.setHomeAddress(homeAddress);
//            elderly.setName(elderlyName);
//            elderly.setElderlyClass(elderlyClassValue);
//            elderly.setZoneCode(zoneCode);
//            elderly.setZoneCodePath(zoneCodePath);
//            elderly.setZoneName(zoneName);
//            elderly.setGovernmentCode(staff.getGovernmentCode());
//            //elderly.setStatus(1);
//            elderly.setCreateTime(new Date());
//            elderlyList.add(elderly);
//
//            User user = new User();
//            user.setUserCode(elderly.getUserCode());
//            user.setPhone(elderly.getPhone());
//            user.setPassword(getMD5Str(elderly.getPhone().substring(5, 11)));
//            user.setName(elderly.getName());
//            user.setStatus(1);
//            user.setCreateTime(new Date());
//            user.setUserType(1);
//            user.setStaffCode(staffCode);
//            user.setGovernmentCode(staff.getGovernmentCode());
//            userList.add(user);
//
//
//        }
//
//
//        if(!errorName.equals("")||!errorPhone.equals("")||!errorIdCard.equals("")||!errorHomeAddress.equals("")||!errorElderlyClass.equals("")||!errorZone.equals("")){
//            return MyResult.Failed("表格中姓名格式不正确：第"+errorName+"行" +
//                    "<br>表格中有格式不正确的手机号码：第"+errorPhone+"行" +
//                    "<br>表格中有格式不正确的身份证：第"+errorIdCard+"行" +
//                    "<br>表格中有家庭地址为空：第"+errorHomeAddress+"行" +
//                    "<br>表格中有不存在的老人类别：第"+errorElderlyClass+"行"+
//                    "<br>表格中有不存在的所属区域：第"+errorZone+"行");
//        }
//        if(!repetitionPhone.equals("")||!repetitionIdCard.equals("")){
//            return MyResult.Failed("表格中有重复的手机号码："+repetitionPhone+",<br>表格中有重复的身份证："+repetitionIdCard);
//        }
//
//        String databasePhone="";
//        String databaseIdCard="";
//
//        int index = elderlyList.size() / 500;
//        for (int i=0;i< index+1;i++) {
//            List<Elderly> elderlyList1= elderlyList.stream().skip(i * 500).limit(500).collect(Collectors.toList());
//            List<String> idcardList = elderlyMapper.batchSelectPhone(elderlyList1);
//            for(String idcard :idcardList){
//                databaseIdCard=databaseIdCard+"、"+idcard;
//            }
//            List<String> phoneList = userMapper.batchSelectPhone(elderlyList1);
//            for(String phone :phoneList){
//                databasePhone=databasePhone+"、"+phone;
//            }
//        }
//        if(!databasePhone.equals("")||!databaseIdCard.equals("")){
//            return MyResult.Failed("手机号码已注册："+databasePhone+",<br>身份证号码已注册："+databaseIdCard);
//        }
//
//
//        for (int i=0;i< index+1;i++) {
//            //stream流表达式，skip表示跳过前i*10000条记录，limit表示读取当前流的前10000条记录
//            elderlyMapper.batchInsert(elderlyList.stream().skip(i * 500).limit(500).collect(Collectors.toList()));
//            userMapper.batchInsert(userList.stream().skip(i * 500).limit(500).collect(Collectors.toList()));
//        }


        return MyResult.Success("上传成功");
    }

    @Override
    public
    MyResult batchImportNew(String fileName, MultipartFile file, String staffCode, InputStream is) throws Exception {

//        List<Elderly> elderlyList = new ArrayList<>();
//        List<User> userList = new ArrayList<>();
//
//        Staff staff = staffMapper.getByStaffCode(staffCode);
//        List<Zone> zoneList = zoneMapper.getListByGovernmentCode(staff.getGovernmentCode());
//        List<ElderlyClass> elderlyClassList = elderlyClassMapper.getList(staff.getGovernmentCode());
//
//        String roleCode = staff.getRoleCode();
//        if (roleCode == null) {
//            return MyResult.Failed("该角色不存在");
//        }
//
//        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
//            throw new Exception("上传文件格式不正确");
//        }
//
//        boolean isExcel2003 = true;
//        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
//            isExcel2003 = false;
//        }
//
//
//        Workbook wb = null;
//        if (isExcel2003) {
//            wb = new HSSFWorkbook(is);
//        } else {
//            wb = new XSSFWorkbook(is);
//        }
//
//        Sheet sheet = wb.getSheetAt(0);
//        if (sheet == null) {
//            return MyResult.Failed("文件不存在！");
//        }
//
//
//
//        HashMap<String,String> idCardHashMap =new HashMap<>();
//
//        String repetitionIdCard="";
//        String errorName="";
//        String errorPhone="";
//        String errorIdCard="";
//        String errorHomeAddress="";
//        String errorElderlyClass="";
//        String errorZone="";
//
//        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
//
//            if(r==1){
//                continue;
//            }
//
//            Row row = sheet.getRow(r); //通过sheet表单对象 得到 行对象
//
//            if (row == null) {
//                continue;
//            }
//
//            for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
//                if (row.getCell(cellIndex) == null) {
//                    continue;
//                }
//            }
//
//
//            boolean isError=false;
//
//            if(row.getCell(0)==null){
//                continue;
//            }
//
//            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//第1格
//            String elderlyName = row.getCell(0).getStringCellValue().replace(" ", "");//得到每一行第一个单元格的值
//            if (elderlyName == null || elderlyName.isEmpty()) {
//                isError=true;
//                errorName=errorName.length()>0?errorName+"、"+(r + 1):(r + 1)+"";
//            }
//
//            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); //第2格
//            String phone = row.getCell(1).getStringCellValue().replace(" ", "");
//            if (phone.trim().length() != 11) {
//                isError=true;
//                errorPhone=errorPhone.length()>0?errorPhone+"、"+(r + 1):(r + 1)+"";
//            }
//
//            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//第3格
//            String idCard = row.getCell(2).getStringCellValue().replace(" ", "");
//            if (!check(idCard)) {
//                isError=true;
//                errorIdCard=errorIdCard.length()>0?errorIdCard+"、"+(r + 1):(r + 1)+"";
//            }
//
//
//            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); //第4格
//            String homeAddress = row.getCell(3).getStringCellValue();
////            if (homeAddress == null || homeAddress.isEmpty()) {
////                isError=true;
////                errorHomeAddress=errorHomeAddress.length()>0?errorHomeAddress+"、"+(r + 1):(r + 1)+"";
////            }
//
//
//            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//第5格
//            String elderlyClass = row.getCell(4).getStringCellValue().replace(" ", "");
//            int elderlyClassValue=-1;
//            for(ElderlyClass elderlyClass1:elderlyClassList){
//                if(elderlyClass1.getName().equals(elderlyClass)){
//                    elderlyClassValue=elderlyClass1.getValue();
//                }
//            }
//            if(elderlyClassValue==-1){
//                errorElderlyClass=errorElderlyClass.length()>0?errorElderlyClass+"、"+(r + 1):(r + 1)+"";
//                isError=true;
//            }
//
//            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);//第6格
//            String zone = row.getCell(5).getStringCellValue().replace(" ", "");
//            String zoneCode="";
//            String zoneNameLast="";
//            String zoneCodePath ="";
//            String zoneName ="";
//            for(Zone zone1:zoneList){
//                if(zone1.getName().equals(zone)){
//                    zoneCode=zone1.getZoneCode();
//                    zoneNameLast=zone1.getName();
//                }
//            }
//            if(zoneCode.equals("")){
//                errorZone=errorZone.length()>0?errorZone+"、"+(r + 1):(r + 1)+"";
//                isError=true;
//            }else {
//                zoneCodePath = getZonePathAndName(zoneList,zoneCode,1,zoneCode);
//                zoneName = getZonePathAndName(zoneList,zoneCode,2,zoneNameLast);
//            }
//
//
//
//
//            if(!idCardHashMap.containsKey(idCard)){
//                idCardHashMap.put(idCard,idCard);
//            }else {
//                isError=true;
//                repetitionIdCard=repetitionIdCard.equals("")?idCard:repetitionIdCard+"、"+idCard;
//            }
//
//
//            if(isError){
//                continue;
//            }
//
//
//            String idNumber=idCard.length()==15?contertToNewCardNumber(idCard):idCard;
//            String genderNumber = idNumber.substring(16, 17);
//            int gender=Integer.parseInt(genderNumber)%2;
//            int age = judgeAge(idNumber);
//
//            Elderly elderly = new Elderly();
//            elderly.setElderlyCode(UUID.randomUUID().toString());
//            elderly.setIdCard(idCard);
//            elderly.setUserCode(UUID.randomUUID().toString());
//            elderly.setGender(gender);
//            elderly.setBirthday(getBirthday(idNumber));
//            elderly.setAge(age);
//            elderly.setPhone(phone);
//            elderly.setHomeAddress(homeAddress);
//            elderly.setName(elderlyName);
//            elderly.setElderlyClass(elderlyClassValue);
//            elderly.setZoneCode(zoneCode);
//            elderly.setZoneCodePath(zoneCodePath);
//            elderly.setZoneName(zoneName);
//            elderly.setGovernmentCode(staff.getGovernmentCode());
//            //elderly.setStatus(1);
//            elderly.setCreateTime(new Date());
//            elderlyList.add(elderly);
//
//            User user = new User();
//            user.setUserCode(elderly.getUserCode());
//            user.setLoginName(idCard);
//            user.setPhone(elderly.getPhone());
//            user.setPassword(getMD5Str(elderly.getPhone().substring(5, 11)));
//            user.setName(elderly.getName());
//            user.setStatus(1);
//            user.setCreateTime(new Date());
//            user.setUserType(1);
//            user.setStaffCode(staffCode);
//            user.setGovernmentCode(staff.getGovernmentCode());
//            userList.add(user);
//
//        }
//
//
//        if(!errorName.equals("")||!errorPhone.equals("")||!errorIdCard.equals("")||!errorHomeAddress.equals("")||!errorElderlyClass.equals("")||!errorZone.equals("")){
//            return MyResult.Failed("表格中姓名格式不正确：第"+errorName+"行" +
//                    "<br>表格中有格式不正确的手机号码：第"+errorPhone+"行" +
//                    "<br>表格中有格式不正确的身份证：第"+errorIdCard+"行" +
//                    "<br>表格中有家庭地址为空：第"+errorHomeAddress+"行" +
//                    "<br>表格中有不存在的老人类别：第"+errorElderlyClass+"行"+
//                    "<br>表格中有不存在的所属区域：第"+errorZone+"行");
//        }
//
//
//        String databasePhone="";
//        String databaseIdCard="";
//
//        int index = elderlyList.size() / 500;
//        for (int i=0;i< index+1;i++) {
//            List<Elderly> elderlyList1= elderlyList.stream().skip(i * 500).limit(500).collect(Collectors.toList());
//
//            List<String> idcardList = elderlyMapper.batchSelectIdCard(elderlyList1);
//            for(String idcard :idcardList){
//                databaseIdCard=databaseIdCard+"、"+idcard;
//            }
//
////            List<String> phoneList = userMapper.batchSelectPhone(elderlyList1);
////            for(String phone :phoneList){
////                databasePhone=databasePhone+"、"+phone;
////            }
//
//        }
////        if(!databasePhone.equals("")||!databaseIdCard.equals("")){
////            return MyResult.Failed("手机号码已注册："+databasePhone+",<br>身份证号码已注册："+databaseIdCard);
////        }
//        if(!databaseIdCard.equals("")){
//            return MyResult.Failed("身份证号码已注册："+databaseIdCard);
//        }
//
//
//        for (int i=0;i< index+1;i++) {
//            //stream流表达式，skip表示跳过前i*10000条记录，limit表示读取当前流的前10000条记录
//            elderlyMapper.batchInsert(elderlyList.stream().skip(i * 500).limit(500).collect(Collectors.toList()));
//            userMapper.batchInsert(userList.stream().skip(i * 500).limit(500).collect(Collectors.toList()));
//        }

        return MyResult.Success("上传成功");
    }






    //获取组织树1、Path,2、Name
    public static String getZonePathAndName(List<Zone> zoneList,String zoneCode,int type,String data){
        for (Zone zone:zoneList){
            if(zone.getZoneCode().equals(zoneCode)){
                if (type==1){
                    data=zone.getZoneCode()+","+data;
                }else {
                    data=zone.getName()+","+data;
                }
                if(!zone.getParentZoneCode().equals("00000000-0000-0000-0000-000000000000")){
                    data=getZonePathAndName(zoneList,zone.getParentZoneCode(),type,data);
                }
                continue;
            }
        }
        return data;
    }

    //身份证号验证
    public static boolean check(String cardNumber){
        if (null != cardNumber){
            cardNumber = cardNumber.trim();
            if (15 == cardNumber.length()){
                cardNumber = contertToNewCardNumber(cardNumber);
            }
            return validate(cardNumber);
        }
        return false;
    }

    public static boolean validate(String cardNumber){
        boolean result = true;
        result = result && (null != cardNumber); // 身份证号不能为空
        result = result && 18 == cardNumber.length(); // 身份证号长度是18(新证)
        // 身份证号的前17位必须是阿拉伯数字
        for (int i = 0; result && i < 18 - 1; i++){
            char ch = cardNumber.charAt(i);
            result = result && ch >= '0' && ch <= '9';
        }
        // 身份证号的第18位校验正确
        result = result
                && (calculateVerifyCode(cardNumber) == cardNumber
                .charAt(18 - 1));
        // 出生日期不能晚于当前时间，并且不能早于1900年
        try{
            Date birthDate = new SimpleDateFormat("yyyyMMdd")
                    .parse(getBirthDayPart(cardNumber));
            result = result && null != birthDate;
            result = result && birthDate.before(new Date());
            result = result && birthDate.after(new Date(-2209017600000L));
            /**
             * 出生日期中的年、月、日必须正确,比如月份范围是[1,12],
             * 日期范围是[1,31]，还需要校验闰年、大月、小月的情况时，
             * 月份和日期相符合
             */
            String birthdayPart = getBirthDayPart(cardNumber);
            String realBirthdayPart = new SimpleDateFormat("yyyyMMdd")
                    .format(birthDate);
            result = result && (birthdayPart.equals(realBirthdayPart));
        }catch(Exception e){
            result = false;
        }
        return result;
    }

    private static String getBirthDayPart(String cardNumber){
        return cardNumber.substring(6, 14);
    }

    /**
     * 校验码（第十八位数）：
     *
     * 十七位数字本体码加权求和公式 S = Sum(Ai * Wi), i = 0...16 ，先对前17位数字的权求和；
     * Ai:表示第i位置上的身份证号码数字值 Wi:表示第i位置上的加权因子 Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4
     * 2; 计算模 Y = mod(S, 11)< 通过模得到对应的校验码 Y: 0 1 2 3 4 5 6 7 8 9 10 校验码: 1 0 X 9
     * 8 7 6 5 4 3 2
     *
     * @param cardNumber
     * @return
     */
    private static char calculateVerifyCode(CharSequence cardNumber){
        int sum = 0;
        for (int i = 0; i < 18 - 1; i++){
            char ch = cardNumber.charAt(i);
            sum += ((int) (ch - '0')) * VERIFY_CODE_WEIGHT[i];
        }
        return VERIFY_CODE[sum % 11];
    }

    /**
     * 把15位身份证号码转换到18位身份证号码<br>
     * 15位身份证号码与18位身份证号码的区别为：<br>
     * 1、15位身份证号码中，"出生年份"字段是2位，转换时需要补入"19"，表示20世纪<br>
     * 2、15位身份证无最后一位校验码。18位身份证中，校验码根据根据前17位生成
     *
     * @param
     * @return
     */
    private static String contertToNewCardNumber(String oldCardNumber){
        StringBuilder buf = new StringBuilder(18);
        buf.append(oldCardNumber.substring(0, 6));
        buf.append("19");
        buf.append(oldCardNumber.substring(6));
        buf.append(calculateVerifyCode(buf));
        return buf.toString();
    }

    //md5 加密
    protected String getMD5Str(String str) {
        MessageDigest messageDigest = null;

        try {
            messageDigest = MessageDigest.getInstance("MD5");
            messageDigest.reset();
            messageDigest.update(str.getBytes("UTF-8"));
        } catch (NoSuchAlgorithmException e) {
            System.out.println("NoSuchAlgorithmException caught!");
            System.exit(-1);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        byte[] byteArray = messageDigest.digest();
        StringBuffer md5StrBuff = new StringBuffer();
        for (int i = 0; i < byteArray.length; i++) {
            if (Integer.toHexString(0xFF & byteArray[i]).length() == 1)
                md5StrBuff.append("0").append(Integer.toHexString(0xFF & byteArray[i]));
            else
                md5StrBuff.append(Integer.toHexString(0xFF & byteArray[i]));
        }
        return md5StrBuff.toString();
    }

    //判断生日
    public Date getBirthday(String idCard) {
        Date birthday = null;
        try {
            birthday = new SimpleDateFormat("yyyyMMdd").parse(getBirthDayPart(idCard));
        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException("身份证号不合法");
        }
        return birthday;
    }

    //判断年龄
    public int judgeAge(String idCard) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date();
        String nowTime = simpleDateFormat.format(date);
        String nowTimeNumber = nowTime.substring(0, 4);
        int nowTimeNum = Integer.parseInt(nowTimeNumber);
        String ageNumber = idCard.substring(6, 10);
        int ageNum = Integer.parseInt(ageNumber);
        int age=nowTimeNum - ageNum;
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.YEAR, -age);
        calendar.getTimeInMillis();
        date.getTime();
        if((calendar.getTimeInMillis()-date.getTime())<0){
            age--;
        }
        return age;
    }



    @Override
    @Transactional
    public MyResult batchRechargeImport(String fileName, MultipartFile file, String staffCode,InputStream is) throws Exception {
//        List<ElderlyDTO> elderlyList = new ArrayList<>();
//        Staff staff = staffMapper.getByStaffCode(staffCode);
//        String governmentCode = staff.getGovernmentCode();
//        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
//            throw new Exception("上传文件格式不正确");
//        }
//        boolean isExcel2003 = true;
//        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
//            isExcel2003 = false;
//        }
//
//
//        Workbook wb = null;
//        if (isExcel2003) {
//            wb = new HSSFWorkbook(is);
//        } else {
//            wb = new XSSFWorkbook(is);
//        }
//
//        Sheet sheet = wb.getSheetAt(0);
//        if (sheet == null) {
//            return MyResult.Failed("文件不存在！");
//        }
//
//
//        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
//            if(r==1){
//                continue;
//            }
//            Row row = sheet.getRow(r); //通过sheet表单对象 得到 行对象
//            if (row == null) {
//                continue;
//            }
//            for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
//                if (row.getCell(cellIndex) == null) {
//                    continue;
//                }
//            }
//            if(row.getCell(0)==null){
//                continue;
//            }
//
//            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
//
//
//            String elderlyName = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
//
//            if (elderlyName == null || elderlyName.isEmpty()) {
//                continue;
//            }
//
//
//            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
//            String idCard = null;
//            idCard = row.getCell(1).getStringCellValue();  //第二格
//            ElderlyDTO elderly =elderlyMapper.getByIdCard(idCard.trim());
//
//
//            if(elderly!=null){
//                elderlyList.add(elderly);
//            }else {
//                return MyResult.Failed("充值失败,第" + (r + 1) + "行,该老人不是社会化服务对象！");
//            }
//
//            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); //第三格
//
//            String addBalance1 = row.getCell(2).getStringCellValue();
//
//            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); //第四格
//
//            String payMoney1 = row.getCell(3).getStringCellValue();
//
//
//
//
//            double addBalance=Double.valueOf(addBalance1).doubleValue();
//            double payMoney=Double.valueOf(payMoney1).doubleValue();
//            elderly.setAddBalance(addBalance);
//            elderly.setPayMonth(payMoney);
//        }
//        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
//        String date =formatter.format(new Date());
//
//        RechargeGroup rechargeGroup =new RechargeGroup();
//        rechargeGroup.setRechargeGroupCode(UUID.randomUUID().toString());
//        rechargeGroup.setName(date);
//        rechargeGroup.setCreateTime(new Date());
//        rechargeGroup.setStaffCode(staffCode);
//        rechargeGroup.setGovernmentCode(governmentCode);
//        rechargeGroup.setRechargeType(2);
//        rechargeGroup.setTotalElderly(elderlyList.size());
//
//        double totalMoney=0.0;
//        for(ElderlyDTO elderlyDTO:elderlyList){
//            double balance =elderlyDTO.getBalance();
//            balance=balance+elderlyDTO.getAddBalance();
//            elderlyDTO.setBalance(balance);
//            String rechargeCode = UUID.randomUUID().toString();
//            RechargeDTO rechargeDTO = new RechargeDTO();
//            rechargeDTO.setRechargeCode(rechargeCode);
//            rechargeDTO.setElderlyCode(elderlyDTO.getElderlyCode());
//            rechargeDTO.setPayMoney(elderlyDTO.getPayMonth());
//            rechargeDTO.setAddBalance(elderlyDTO.getAddBalance());
//            rechargeDTO.setCreateTime(new Date());
//            rechargeDTO.setStaffCode(staffCode);
//            rechargeDTO.setGovernmentCode(governmentCode);
//            rechargeDTO.setType(1);
//            rechargeDTO.setRechargeGroupCode(rechargeGroup.getRechargeGroupCode());
//
//            totalMoney=totalMoney+elderlyDTO.getAddBalance();
//
//            elderlyMapper.update(elderlyDTO);
//            rechargeMapper.insert(rechargeDTO);
//        }
//        System.out.println("totalMoney = " + totalMoney);
//        rechargeGroup.setTotalMoney(totalMoney);
//        rechargeGroupMapper.insert(rechargeGroup);
        return MyResult.Success("充值成功！");

    }



}
